from quant_researcher.quant.project_tool.time_tool import get_yesterday
from quant_researcher.quant.datasource_fetch.crypto_api.glassnode import get_prices
from quant_researcher.quant.datasource_fetch.crypto_api.binance import fetch_binance_margin_interest_rate
from quant_researcher.quant.datasource_fetch.crypto_api.okex.okex import fetch_okex_margin_interest_rate
from quant_researcher.quant.datasource_fetch.crypto_api.loanscan import get_lend_borrow_fee_rate
from quant_researcher.quant.datasource_fetch.crypto_api.aave import get_aave_borrow_fee_rate
from quant_researcher.quant.datasource_fetch.crypto_api.aave import get_aave_borrow_amount
import numpy as np
import os
import pandas as pd
import time
import datetime
from dateutil.tz import tzutc


def get_binance_margin_interest_rate():
    end_date = get_yesterday(marker='with_n_dash')  # 计算截止昨日收盘
    price_log_price = get_prices(ohlc=False, asset='BTC', start_date='2018-01-01', end_date=end_date)
    price_log_price['log_prices'] = np.log10(price_log_price['close'])

    # 分析币安杠杆交易费率
    file_path = os.path.join("G:/", f'margin_interest_rate')
    os.makedirs(file_path, exist_ok=True)
    file_name = os.path.join(file_path, f'binance_margin_interest_rate')
    if os.path.exists(f'{file_name}.xlsx'):
        history_binance_margin_interest_rate = pd.read_excel(f'{file_name}.xlsx', index_col='end_date')
        latest_date = history_binance_margin_interest_rate.index[-7]
        start = time.mktime(datetime.datetime(int(latest_date[:4]), int(latest_date[5:7]), int(latest_date[-2:]), 0, 0, 0, tzinfo=tzutc()).timetuple())
    else:
        start = time.mktime(datetime.datetime(2018, 1, 1, 0, 0, 0, tzinfo=tzutc()).timetuple())
    end = time.mktime(datetime.datetime(int(end_date[:4]), int(end_date[5:7]), int(end_date[-2:]), 23, 59, 59, tzinfo=tzutc()).timetuple())
    df_list = []
    for asset in ['USDT', 'BUSD', 'BTC', 'ETH']:
        print(f'开始从binance获取{asset}的借贷费率')
        frame = fetch_binance_margin_interest_rate(asset=asset, start_time=start, end_time=end)  # https://www.binance.com/en/margin/interest-history
        frame['start_time'] = frame.apply(lambda x: int(x['timestamp'] / 1000), axis=1)
        frame['end_date'] = pd.to_datetime(frame['start_time'], unit='s').dt.tz_localize('UTC').dt.tz_convert('+0000')
        frame['end_date'] = frame['end_date'].dt.strftime('%Y-%m-%d')
        frame['datetime'] = pd.to_datetime(frame['start_time'], unit='s').dt.tz_localize('UTC').dt.tz_convert('+0000')
        frame['datetime'] = frame['datetime'].dt.strftime('%Y-%m-%d %H:%M:%S')
        frame = frame[~frame.duplicated(subset='end_date')]  # 一天可能有多个费率数据
        frame.set_index('end_date', inplace=True)
        frame.rename(columns={'margin_interest_rate': f'{asset}_margin_interest_rate'}, inplace=True)
        df_list.append(frame[f'{asset}_margin_interest_rate'])
    frame = pd.concat(df_list, axis=1)
    all_new_frame = frame.merge(price_log_price, left_index=True, right_index=True)
    if os.path.exists(f'{file_name}.xlsx'):
        all_new_frame = pd.concat([history_binance_margin_interest_rate, all_new_frame], axis=0)
        all_new_frame = all_new_frame[~all_new_frame.index.duplicated(keep='last')]  # index去重
    all_new_frame.to_excel(f'{file_name}.xlsx')


def get_okex_margin_interest_rate():
    # 分析okx杠杆交易费率
    okex_margin_interest_rate = fetch_okex_margin_interest_rate()
    file_path = os.path.join("G:/", f'margin_interest_rate')
    file_name = os.path.join(file_path, f'okx_margin_interest_rate.xlsx')
    if os.path.exists(file_name):
        historical_data = pd.read_excel(file_name)
        if max(historical_data['end_date']) < max(okex_margin_interest_rate['end_date']):
            all_df = pd.concat([historical_data, okex_margin_interest_rate], axis=0, ignore_index=True)
            all_df.to_excel(f'{file_name}', index=False)
        else:
            pass
    else:
        okex_margin_interest_rate.to_excel(f'{file_name}', index=False)


def get_loanscan_margin_interest_rate():
    end_date = get_yesterday(marker='with_n_dash')  # 计算截止昨日收盘
    price_log_price = get_prices(ohlc=False, asset='BTC', start_date='2018-01-01', end_date=end_date)
    price_log_price['log_prices'] = np.log10(price_log_price['close'])

    # 分析去中心化借贷平台费率(从loanscan下载数据)
    for protocol in ['aave', 'compound', 'dydx', 'makerdao']:
        df_list = []
        for lend_or_borrow in ['lend', 'borrow']:
            for asset in ['USDC', 'DAI', 'ETH', 'WBTC']:
                df = get_lend_borrow_fee_rate(protocol=protocol, lend_or_borrow=lend_or_borrow, asset=asset)
                if df is not None:
                    df.rename(columns={f'{lend_or_borrow}_rate': f'{protocol}_{asset}_{lend_or_borrow}_rate'}, inplace=True)
                    df_list.append(df)
        all_df = pd.concat(df_list, axis=1)
        all_frame = all_df.merge(price_log_price, left_index=True, right_index=True)
        file_path = os.path.join("G:/", f'margin_interest_rate')
        os.makedirs(file_path, exist_ok=True)
        file_name = os.path.join(file_path, f'loanscan_{protocol}_margin_interest_rate')
        all_frame.to_excel(f'{file_name}.xlsx')

def get_aave_margin_tvl_brrowed():

    # 分析去中心化借贷平台aave
    end_date = get_yesterday(marker='with_n_dash')  # 计算截止昨日收盘
    price_log_price = get_prices(ohlc=False, asset='BTC', start_date='2018-01-01', end_date=end_date)
    price_log_price['log_prices'] = np.log10(price_log_price['close'])
    for asset in ['USDC', 'DAI', 'ETH', 'WBTC']:
        df = get_aave_borrow_fee_rate(asset=asset)
        df = df.groupby(['date'])[df.columns[:4]].mean()
        all_frame = df.merge(price_log_price, left_index=True, right_index=True)
        file_path = os.path.join("G:/", f'margin_interest_rate')
        os.makedirs(file_path, exist_ok=True)
        file_name = os.path.join(file_path, f'aave_{asset}_margin_interest_rate')
        all_frame.to_excel(f'{file_name}.xlsx')
        time.sleep(5)

    df = get_aave_borrow_amount()
    all_frame = df.merge(price_log_price, left_index=True, right_index=True)
    all_frame.sort_index(inplace=True)
    file_name = os.path.join(file_path, f'aave_margin_tvl_borrowed')
    all_frame.to_excel(f'{file_name}.xlsx')


def get_exchange_margin_interest_rate():
    get_loanscan_margin_interest_rate()


if __name__ == "__main__":
    get_exchange_margin_interest_rate()